package edu.zut.cs.software.example.square.dao;

import java.util.List;


import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import edu.zut.cs.software.example.dao.GenericDao;
import edu.zut.cs.software.example.square.domain.Square;




/**
 * 
 * @author zhangjiahua
 *
 */

@Repository
public interface SquareDao extends GenericDao<Square, Long> {
	
	
	/**
	 * Get all the study notes
	 * 
	 * 
	 * @param pagenum
	 * @param pageSize
	 * @return
	 */
	/*@Query(value = "  SELECT * FROM `example`.`t_square` WHERE `studentid`= ?1 LIMIT ?2,?3 ", nativeQuery = true)
	public List<Square> findByStudentId(@Param("studentid")Long studentid,@Param("pagenum") int pagenum,@Param("pageSize") int pageSize);
	
	
	/**
	 * 
	 * @param studentid
	 * @return
	 
	@Query(value = "  SELECT count(*) FROM `example`.`t_square` WHERE `studentid` = ?1 ", nativeQuery = true)
	public int getSquareByStudentIdCounts(@Param("studentid") Long studentid);*/
	
	/**
	 *  
	 * 
	 * @param title
	 * @return
	 */
	@Query(value="  SELECT * FROM example.t_square LIMIT ?1,?2",nativeQuery = true)
	public  List<Square> getBySquare( @Param("pagenum") int pagenum,@Param("pageSize") int pageSize) ;
	
	/**
	 * 
	 * @return
	 */
	@Query(value = "  SELECT count(*) FROM `example`.`t_square`  ", nativeQuery = true)
	public int getSquareCounts();
	@Query(value = "  SELECT count(*) FROM `example`.`t_square` WHERE `title`LIKE CONCAT('%',?1,'%') ", nativeQuery = true)
	public int getSquareBytitleCounts(@Param("title") String title);
	
   
	/**
	 * 
     * 
     * @param title
     * @param pagenum
     * @param pageSize
     * @return
     */
	@Query(value = "  SELECT * FROM `example`.`t_square` WHERE `title` LIKE CONCAT('%',?1,'%')  LIMIT ?2,?3 ", nativeQuery = true)
	public List<Square> findBytitle(@Param("title")String title,@Param("pagenum") int pagenum,@Param("pageSize") int pageSize);
    
	/**
	 * 
	 * @param id
	 * @return
	 */
	@Query(value = " SELECT * FROM `example`.`t_square` WHERE `ID`=?1 ", nativeQuery = true)
	public Square getUpdateSquare(@Param("id") int id);
	/**
	 * 
	 * 
	 * @param id
	 * @return
	 */
	@Modifying
	@Transactional
	@Query(value = "  DELETE  FROM `example`.`t_square` WHERE `ID`=?1 ", nativeQuery = true)
	public int deleteById(@Param("id")int id);
    
	/**
	 * 
	 * @param content
	 * @param title
	 * @param id
	 * @return
	 */
	@Query(value = "  UPDATE `example`.`t_square` SET content=:content , title=:title WHERE ID=:id  ", nativeQuery = true)
	@Modifying
	@Transactional
	public int editsquare(@Param("content") String content,@Param("title")String title,@Param("id")Long id);

	
	


}
